
. 
. 
.         use Data\ixd_1_trim_2008.dta, clear

.         append using Data\ixd_2_trim_2008.dta

.         append using Data\ixd_1_trim_2009.dta

.         append using Data\ixd_2_trim_2009.dta

.         gen byte EU=0

.         replace EU=1 if flow=="II" | flow=="XI"
(4336043 real changes made)

.         replace flow="IMP" if flow=="II" | flow=="IE"
flow was str2 now str3
(2953140 real changes made)

.         replace flow="EXP" if flow=="XI" | flow=="XE"
(2753746 real changes made)

. 
.         egen uncontr_tot_trade=sum(value), by(year flow)

.         tab uncontr_tot_trade year if flow=="EXP"

uncontr_to |         year
   t_trade |      2008       2009 |     Total
-----------+----------------------+----------
  1.25e+11 |         0  1,369,151 | 1,369,151 
  1.63e+11 | 1,384,595          0 | 1,384,595 
-----------+----------------------+----------
     Total | 1,384,595  1,369,151 | 2,753,746 


.         
.         tab uncontr_tot_trade year if flow=="IMP"

uncontr_to |         year
   t_trade |      2008       2009 |     Total
-----------+----------------------+----------
  1.18e+11 |         0  1,465,598 | 1,465,598 
  1.58e+11 | 1,487,542          0 | 1,487,542 
-----------+----------------------+----------
     Total | 1,487,542  1,465,598 | 2,953,140 


.         
.         drop uncontr_tot_trade

. 
. 
.         gen chow = 1 if  ///
>                          nature == "1" | nature == "11" | nature == "12" | nature == "13" | nature == "14" | nature == "15"
>  | nature == "16" | nature == "17" | ///
>                          nature == "18" | nature == "19"  | ///
>                          nature == "3" | nature == "31" | nature == "32" | nature == "33" | nature == "34" | nature == "35"
>  | nature == "36" | nature == "37" | ///
>                          nature == "38" | nature == "39"  | ///
>                          nature == "7" | nature == "71" | nature == "72" | nature == "73" | nature == "74" | nature == "75"
>  | nature == "76" | nature == "77" | ///
>                          nature == "78" | nature == "79"  | ///
>                          nature ==  "8" | nature ==  "81" | nature ==  "82" | nature ==  "83" | nature ==  "84" | nature ==
>   "85" | nature ==  "86" | ///
>                          nature == "87" | nature ==  "88" | nature ==  "89"
(676295 missing values generated)

.         
.         qui count if chow == 1

.         
.         *******keeping only those transactins involving changes in ownerships*****
.         keep if chow == 1 
(676295 observations deleted)

.         drop chow

.         egen uncontr_tot_trade=sum(value), by(year flow)

.         tab uncontr_tot_trade year if flow=="EXP"

uncontr_to |         year
   t_trade |      2008       2009 |     Total
-----------+----------------------+----------
  1.05e+11 |         0  1,206,313 | 1,206,313 
  1.39e+11 | 1,214,357          0 | 1,214,357 
-----------+----------------------+----------
     Total | 1,214,357  1,206,313 | 2,420,670 


.         
.         tab uncontr_tot_trade year if flow=="IMP"

uncontr_to |         year
   t_trade |      2008       2009 |     Total
-----------+----------------------+----------
  1.02e+11 |         0  1,297,658 | 1,297,658 
  1.35e+11 | 1,312,263          0 | 1,312,263 
-----------+----------------------+----------
     Total | 1,312,263  1,297,658 | 2,609,921 


.         
.         drop uncontr_tot_trade nature

.         ***********     
. 
. 
.         **Eliminating some unuseful codes 
.         
.         drop if  land == "EU" |land == "II" | land == "BE" |land == "XX" | land == "QQ" | land == "QU" | land =="QX" |  lan
> d =="QY" |  land =="QZ" | land =="XN" |  land =="XU" |  land =="XV" |  land =="XW" |  land =="XS" |  land =="XR" |  land ==
> "XQ" |  land =="XP" |  land =="XO" |  land =="XT"   
(9549 observations deleted)

.         egen uncontr_tot_trade=sum(value), by(year flow)

.         tab uncontr_tot_trade year if flow=="EXP"

uncontr_to |         year
   t_trade |      2008       2009 |     Total
-----------+----------------------+----------
  1.04e+11 |         0  1,202,159 | 1,202,159 
  1.37e+11 | 1,209,854          0 | 1,209,854 
-----------+----------------------+----------
     Total | 1,209,854  1,202,159 | 2,412,013 


.         
.         tab uncontr_tot_trade year if flow=="IMP"

uncontr_to |         year
   t_trade |      2008       2009 |     Total
-----------+----------------------+----------
  1.02e+11 |         0  1,297,261 | 1,297,261 
  1.35e+11 | 1,311,768          0 | 1,311,768 
-----------+----------------------+----------
     Total | 1,311,768  1,297,261 | 2,609,029 


.         
.         drop uncontr_tot_trade 

.         
.         **Adding informaton about OECD in 2008
.         
.         sort land

.         merge land using Data\OECD_2008.dta
variable land does not uniquely identify observations in the master data

.         drop if _merge==2
(12 observations deleted)

.         replace  oecd_2008=0 if  oecd_2008==.
(15969 real changes made)

.         drop _merge

.         replace EU=0 if land=="BR" | land=="ID" | land=="CN"
(0 real changes made)

.         egen max_EU=max(EU), by (land)

.         replace EU=max_EU
(0 real changes made)

.         drop max_EU

. 
.         ***drop trade by non residents********
. 
.         sort vat year

.         merge vat year using Data\Non_residents_year_2008.dta
variables vat year do not uniquely identify observations in the master data
year was int now float

.         drop if _merge==2
(2830 observations deleted)

.         drop _merge

.         gen keep=1

.         replace keep=0 if resd!=""
(241234 real changes made)

.         drop  resd

. 
.         sort vat year

.         merge vat year using Data\Non_residents_year_2009.dta
variables vat year do not uniquely identify observations in the master data

.         drop if _merge==2
(2948 observations deleted)

.         drop _merge

.         replace keep=0 if resd!=""
(252544 real changes made)

.         drop  resd

. 
.         keep if keep==1
(493778 observations deleted)

. 
. 
. 
.         collapse (sum)  weight units value (mean) EU oecd_2008, by( year vat flow land code) 

.         
. ******************************Trade margins**********************************************
. ******************************Trade margins**********************************************
. 
.         **Building margins
.         sort year flow vat  land code   

.         gen byte firm_counter=0

.         replace firm_counter=1 if vat[_n]!=vat[_n-1]
(101353 real changes made)

.         gen byte country_counter=1

.         replace country_counter=0 if land[_n]==land[_n-1] & vat[_n]==vat[_n-1]
(2553238 real changes made)

.         gen byte product_counter=1

.         replace product_counter=0 if code[_n]==code[_n-1] & land[_n]==land[_n-1] & vat[_n]==vat[_n-1] 
(0 real changes made)

.         gen mix_quantity=units

.         replace mix_quantity= weight if mix_quantity==0 
(2224833 real changes made)

.         replace weight=0 if units>0
(177963 real changes made)

.         drop units

.         save temp1sm.dta, replace
file temp1sm.dta saved

. 
. 
.         ********Data for country Table*********
.         use temp1sm.dta, clear

.         collapse(sum)  value, by( land year)

.         reshape wide  value, i(land) j(year)
(note: j = 2008 2009)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                      450   ->     230
Number of variables                   3   ->       3
j variable (2 values)              year   ->   (dropped)
xij variables:
                                  value   ->   value2008 value2009
-----------------------------------------------------------------------------

.         gen pippo= value2008 +value2009
(10 missing values generated)

.         drop if pippo==.
(10 observations deleted)

.         drop pippo

.         egen rank=rank(-value2008)

.         drop if rank>100
(120 observations deleted)

.         gen drop= ((value2009/ value2008)-1)*100

.         sort rank

. 
.         ********Summary statistics of the trade collapse for different type of goods*********
.         use temp1sm.dta, clear

.         gen HS4=substr(code,1,4)

.         rename HS4  CN4_2008

.         sort   CN4_2008

.         *merge  hs07_4dig using Data\hs07_4dig_bec.dta
.         merge  CN4_2008 using Data\cn_2008_cpa_2002.dta
variable CN4_2008 does not uniquely identify observations in the master data

.         drop if _merge==2
(9 observations deleted)

.         replace Other=1 if _merge==1
(9981 real changes made)

.         recode Intermediate Capital_goods Consumer_dur Consumer_n_dur Energy (.=0) if _merge==1
(Intermediate: 9981 changes made)
(Capital_goods: 9981 changes made)
(Consumer_dur: 9981 changes made)
(Consumer_n_dur: 9981 changes made)
(Energy: 9981 changes made)

.         drop _merge

.         gen good_type=0

.         replace good_type=2 if Intermediate==1 
(1252091 real changes made)

.         replace good_type=3 if Capital_goods==1 
(560618 real changes made)

.         replace good_type=4 if Consumer_dur==1 
(118819 real changes made)

.         replace good_type=1 if Consumer_n_dur==1 
(881625 real changes made)

.         replace good_type=5 if Energy==1 
(15569 real changes made)

.         replace good_type=6 if Other==1
(210284 real changes made)

.         collapse(sum) value ,by(year  flow good_type)

.         sort  flow good_type year

. 
.         ********DECOMPOSITION: ALL COUNTRIES*********
. 
.         use temp1sm.dta, clear

.         egen firms=sum(firm_counter), by (year flow)

.         egen av_country=sum(country_counter/firms), by (year flow)

.         egen av_product=sum(product_counter/(firms*av_country)), by (year flow)

.         egen av_sales=mean(value), by (year flow)

.         egen av_mix_q=mean(mix_quantity), by (year flow)

.         egen av_mix_p=mean(value/av_mix_q), by (year flow)

.         replace weight=. if  weight==0
(814173 real changes made, 814173 to missing)

.         egen av_weight_q=mean(weight), by (year flow)

.         egen av_weight_p=mean(value/av_weight_q) if weight!=., by (year flow)
(814173 missing values generated)

. 
.         collapse (mean) firms av_country av_product av_sales av_mix_q av_mix_p av_weight_q av_weight_p, by(flow year)

. 
end of do-file

. browse

. exit, clear
